#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Database Migration Script
Update metrics table constraint from UNIQUE(name) to UNIQUE(name, type)
"""

import sqlite3
import sys
import logging
from pathlib import Path

# Configure logging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)


def migrate_database(db_path: str):
    """Migrate database to use composite unique constraint."""
    if not Path(db_path).exists():
        logger.error(f"Database file not found: {db_path}")
        return False

    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        logger.info("Starting database migration...")

        # Get all dependent views
        cursor.execute("SELECT name, sql FROM sqlite_master WHERE type='view' AND sql LIKE '%metrics%'")
        views = cursor.fetchall()

        logger.info(f"Found {len(views)} dependent views:")
        for view_name, view_sql in views:
            logger.info(f"  - {view_name}")

        # Drop dependent views temporarily
        for view_name, _ in views:
            cursor.execute(f"DROP VIEW IF EXISTS {view_name}")
            logger.info(f"Dropped view: {view_name}")

        # Check current table structure
        cursor.execute("PRAGMA table_info(metrics)")
        columns = cursor.fetchall()
        logger.info("Current metrics table structure:")
        for col in columns:
            logger.info(f"  {col}")

        # Check for duplicate (name, type) combinations
        cursor.execute("""
            SELECT name, type, COUNT(*) as count
            FROM metrics
            GROUP BY name, type
            HAVING count > 1
        """)
        duplicates = cursor.fetchall()

        if duplicates:
            logger.warning(f"Found {len(duplicates)} duplicate (name, type) combinations:")
            for name, mtype, count in duplicates:
                logger.warning(f"  - {name} ({mtype}): {count} records")

            # Keep the first occurrence of each duplicate
            cursor.execute("""
                DELETE FROM metrics
                WHERE id NOT IN (
                    SELECT MIN(id)
                    FROM metrics
                    GROUP BY name, type
                )
            """)
            deleted_count = cursor.rowcount
            logger.info(f"Removed {deleted_count} duplicate records")

        # Create new table with composite unique constraint
        cursor.execute("""
            CREATE TABLE metrics_new (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                type TEXT NOT NULL CHECK(type IN ('histogram', 'enum', 'ukm_event', 'ukm_metric')),
                summary TEXT,
                units TEXT,
                expires_after TEXT,
                component TEXT,
                singular BOOLEAN DEFAULT FALSE,
                file_path TEXT NOT NULL,
                line_number INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                analysis_status TEXT DEFAULT 'NOT_ANALYZED',
                analysis_notes TEXT,
                analyzed_by TEXT,
                analyzed_at DATETIME,
                UNIQUE(name, type)
            )
        """)

        # Copy all data from old table to new table
        cursor.execute("""
            INSERT INTO metrics_new
            SELECT * FROM metrics
        """)

        # Drop original table
        cursor.execute("DROP TABLE metrics")

        # Rename new table to original name
        cursor.execute("ALTER TABLE metrics_new RENAME TO metrics")

        # Recreate indexes
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_metrics_name ON metrics(name)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_metrics_type ON metrics(type)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_metrics_component ON metrics(component)")

        # Recreate dependent views
        logger.info("Recreating dependent views...")
        for view_name, view_sql in views:
            cursor.execute(view_sql)
            logger.info(f"Recreated view: {view_name}")

        conn.commit()
        logger.info("Migration completed successfully!")

        # Verify the new structure
        cursor.execute("PRAGMA table_info(metrics)")
        columns = cursor.fetchall()
        logger.info("Updated metrics table structure:")
        for col in columns:
            logger.info(f"  {col}")

        # Verify no duplicates exist
        cursor.execute("""
            SELECT COUNT(*) FROM (
                SELECT name, type
                FROM metrics
                GROUP BY name, type
                HAVING COUNT(*) > 1
            )
        """)
        duplicate_count = cursor.fetchone()[0]
        logger.info(f"Duplicate (name, type) combinations after migration: {duplicate_count}")

        return True

    except Exception as e:
        logger.error(f"Migration failed: {e}")
        conn.rollback()
        return False
    finally:
        conn.close()


if __name__ == "__main__":
    # Default database path
    db_path = "/Users/xiaotianzhang/Documents/demo/UmaInsight/backend/data/uma_insight-20251124.db"

    if len(sys.argv) > 1:
        db_path = sys.argv[1]

    logger.info(f"Migrating database: {db_path}")

    if migrate_database(db_path):
        logger.info("Migration completed successfully!")
        sys.exit(0)
    else:
        logger.error("Migration failed!")
        sys.exit(1)